/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.ArrayList;
import model.config.MySQLQuery;
import model.pojo.*;
import model.util.MySQLProviders;

/**
 *
 * @author VuTienIT
 */
public class PhieuDAO {
    
    //<editor-fold defaultstate="collapsed" desc="GetPhieu">
    public static PhieuPOJO GetPhieu(int MaPhieu) throws FileNotFoundException, IOException
    {
        PhieuPOJO p = new PhieuPOJO();
        MySQLQuery mySQLQuery=new MySQLQuery();
        mySQLQuery.Read("Phieu");
        String sql = String.format(mySQLQuery.GetQuery(MySQLQuery.QueryKey.SELECT_PHIEU),MaPhieu);
        sql="Select * from Phieu Where MaPhieu="+MaPhieu;
        try {
            MySQLProviders helper = new MySQLProviders();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next())
            {
                p.setMaPhieu(rs.getInt("MaPhieu"));
                p.setTenPhieu(rs.getString("TenPhieu"));
                p.setMoTa(rs.getString("MoTa"));
                p.setLuuY(rs.getString("LuuY"));
                p.setGiaBan(rs.getDouble("GiaBan"));
                p.setGiaGoc(rs.getDouble("GiaGoc"));
                p.setGiamGia(rs.getInt("GiamGia"));
                p.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                p.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                p.setSoLuongMua(rs.getInt("SoLuongMua"));
                p.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                p.setHinhChinh(rs.getString("HinhChinh"));
                p.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                p.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                p.setMaNCC(rs.getInt("MaNCC"));
                p.setAnHien(rs.getBoolean("AnHien"));
            }
            helper.close();
        } catch (SQLException ex) {
            Logger.getLogger(PhieuDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return p;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="laySoLuongSanPham">
    public static int laySoLuongSanPham() throws FileNotFoundException, IOException {
        int soLuong = -1;
        String sql = "select GiaTri  from thamso where tenthamso='sophieutrentrang'";
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        ResultSet rs = helper.executeQuery(sql);
        try {
         if (rs.next()) {
            soLuong = rs.getInt("GiaTri");
         }
        } catch (SQLException ex) {
         System.out.println(ex.getMessage());
        }
        helper.close();
        return soLuong;
    }
    //</editor-fold>
  
    //<editor-fold defaultstate="collapsed" desc="getDanhSachKMTruoc">
    public static ArrayList<PhieuPOJO> getDanhSachKMTruoc() throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        //MySQLQuery mySQLQuery=new MySQLQuery();
        //mySQLQuery.Read("PHIEU");
        String sql="select * from phieu where AnHien=1 and BatDauKhuyenMai < CURRENT_DATE()-1  Order By BatDauKhuyenMai DESC";
        
        try
        {
            
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachKMTruocPT">
    public static ArrayList<PhieuPOJO> getDanhSachKMTruocPT(int trang) throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        MySQLQuery mySQLQuery=new MySQLQuery();
        mySQLQuery.Read("PHIEU");
        int soSPtrenTrang = PhieuDAO.laySoLuongSanPham();
        int batdau = (trang - 1)*soSPtrenTrang;
        try
        {
            String sql = String.format("Select * From Phieu Where AnHien=1 and BatDauKhuyenMai < CURRENT_DATE()-1  Order By BatDauKhuyenMai DESC Limit %d,%d ",batdau,soSPtrenTrang);
            
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachLoai">
    public static ArrayList<PhieuPOJO> getDanhSachLoai(int maDM) throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        MySQLQuery mySQLQuery=new MySQLQuery();
        mySQLQuery.Read("PHIEU");
        
        try
        {
            String sql =String.format(mySQLQuery.GetQuery(MySQLQuery.QueryKey.SELECT_ALL_PHIEU_DANHMUC),maDM);
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachLoaiPT">
    public static ArrayList<PhieuPOJO> getDanhSachLoaiPT(int maDM, int trang) throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        MySQLQuery mySQLQuery=new MySQLQuery();
        mySQLQuery.Read("PHIEU");
        
        int soSPtrenTrang = PhieuDAO.laySoLuongSanPham();
        int batdau = (trang - 1)*soSPtrenTrang;
        try
        {
            String sql = String.format(mySQLQuery.GetQuery(MySQLQuery.QueryKey.SELECT_ALL_PHIEU_DANHMUC_TRANG),maDM,batdau,soSPtrenTrang);
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachTimKiem">
    public static ArrayList<PhieuPOJO> getDanhSachTimKiem(String tukhoa) throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        MySQLQuery mySQLQuery=new MySQLQuery();
        mySQLQuery.Read("PHIEU");
        try
        {
            String sql = "Select * From Phieu Where AnHien=1 and TenPhieu LIKE concat('%','"+tukhoa+"','%')";
            //String sql =String.format(mySQLQuery.GetQuery(MySQLQuery.QueryKey.TIMKIEM_TEN),str);
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachTimKiemPT">
    public static ArrayList<PhieuPOJO> getDanhSachTimKiemPT(String tukhoa, int trang) throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        MySQLQuery mySQLQuery=new MySQLQuery();
        mySQLQuery.Read("PHIEU");
        
        int soSPtrenTrang = PhieuDAO.laySoLuongSanPham();
        int batdau = (trang - 1)*soSPtrenTrang;
        try
        {
            String sql = "Select * From Phieu Where AnHien=1 and TenPhieu LIKE concat('%','"+tukhoa+"','%')";
            //String sql = String.format(mySQLQuery.GetQuery(MySQLQuery.QueryKey.TIMKIEM_TEN_PHANTRANG),str,batdau,soSPtrenTrang);
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSach">
    public static ArrayList<PhieuPOJO> getDanhSach() throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        //MySQLQuery mySQLQuery=new MySQLQuery();
        //mySQLQuery.Read("PHIEU");
        String sql="select * from phieu where AnHien=1 and BatDauKhuyenMai >= CURRENT_DATE()-1";
        
        try
        {
            
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachPT">
    public static ArrayList<PhieuPOJO> getDanhSachPT(int trang) throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        MySQLQuery mySQLQuery=new MySQLQuery();
        mySQLQuery.Read("PHIEU");
        int soSPtrenTrang = PhieuDAO.laySoLuongSanPham();
        int batdau = (trang - 1)*soSPtrenTrang;
        try
        {
            String sql = String.format(mySQLQuery.GetQuery(MySQLQuery.QueryKey.SELECT_ALL_PHIEU_TRANG_I),batdau,soSPtrenTrang);
            
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachKMHnay3">
    public static ArrayList<PhieuPOJO> getDanhSachKMHnay3() throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        //MySQLQuery mySQLQuery=new MySQLQuery();
        //mySQLQuery.Read("PHIEU");
        String sql="SELECT * FROM `Phieu` WHERE MaPhieu <= (SELECT FLOOR( MAX(MaPhieu) * RAND()) FROM `Phieu` ) "
                + "and BatDauKhuyenMai >= CURRENT_DATE()-1 and AnHien=1 "
                + "ORDER BY BatDauKhuyenMai DESC LIMIT 1,3";
        
        try
        {
            
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachKMTruoc3">
    public static ArrayList<PhieuPOJO> getDanhSachKMTruoc3() throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        //MySQLQuery mySQLQuery=new MySQLQuery();
        //mySQLQuery.Read("PHIEU");
        String sql="SELECT * FROM `Phieu` WHERE MaPhieu >= (SELECT FLOOR( MAX(MaPhieu) * RAND()) FROM `Phieu` ) "
                + "and BatDauKhuyenMai < CURDATE() and KetThucKhuyenMai >= CURDATE() and AnHien=1 "
                + "ORDER BY BatDauKhuyenMai DESC LIMIT 1,3";
        
        try
        {
            
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachKMLoaiTru6">
    public static ArrayList<PhieuPOJO> getDanhSachKMLoaiTru6(int maPhieu) throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        //MySQLQuery mySQLQuery=new MySQLQuery();
        //mySQLQuery.Read("PHIEU");
        String sql="SELECT * FROM `Phieu` WHERE MaPhieu <= (SELECT FLOOR( MAX(MaPhieu) * RAND()) FROM `Phieu` ) "
                + "and KetThucKhuyenMai >= CURDATE() and AnHien=1 and MaPhieu!="+maPhieu+" "
                + "ORDER BY BatDauKhuyenMai DESC LIMIT 1,6";
        
        try
        {
            
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="getDanhSachKMLoaiTruDM3">
    public static ArrayList<PhieuPOJO> getDanhSachKMLoaiTruDM3(int maDM) throws FileNotFoundException, IOException
    {
        ArrayList<PhieuPOJO> s = new ArrayList<PhieuPOJO>();
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        
        //MySQLQuery mySQLQuery=new MySQLQuery();
        //mySQLQuery.Read("PHIEU");
        String sql="SELECT * FROM `Phieu` WHERE MaPhieu <= (SELECT FLOOR( MAX(MaPhieu) * RAND()) FROM `Phieu` ) "
                + "and KetThucKhuyenMai >= CURDATE() and AnHien=1 and MaDanhMuc!="+maDM+" "
                + "ORDER BY BatDauKhuyenMai DESC LIMIT 1,3";
        
        try
        {
            
            ResultSet rs = helper.executeQuery(sql);
            while(rs.next())
            {
                PhieuPOJO temp = new PhieuPOJO();
                temp.setMaPhieu(rs.getInt("MaPhieu"));
                temp.setTenPhieu(rs.getString("TenPhieu"));
                temp.setMoTa(rs.getString("MoTa"));
                temp.setLuuY(rs.getString("LuuY"));
                temp.setGiaBan(rs.getDouble("GiaBan"));
                temp.setGiaGoc(rs.getDouble("GiaGoc"));
                temp.setGiamGia(rs.getInt("GiamGia"));
                temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
                temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
                temp.setSoLuongMua(rs.getInt("SoLuongMua"));
                temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));
                temp.setHinhChinh(rs.getString("HinhChinh"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
                temp.setMaNCC(rs.getInt("MaNCC"));
                temp.setAnHien(rs.getBoolean("AnHien"));
                s.add(temp);
            }
        }
        catch(SQLException ex)
        {
            Logger.getLogger(PhieuPOJO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return s;
    }
    //</editor-fold>
    
    public static boolean KiemTraSoLuongDatHang(int MaPhieu,int SoLuong, MySQLProviders helper) throws SQLException
    {
        boolean flag=false;
        String sql = "Select * From Phieu Where MaPhieu = "+MaPhieu+" and SoLuongQuiDinh - SoLuongMua >= "+SoLuong;
        ResultSet rs = helper.executeQuery(sql);
        if(rs.next())
        {
            flag= true;
        }
        return flag;
    }
    
    public static int tinhsoluongTrang() throws FileNotFoundException, IOException
    {
        int sotrang=0;
        int soSPtrenTrang = PhieuDAO.laySoLuongSanPham(); 
        int soluongSPtrenTrang = soSPtrenTrang;
        ArrayList<PhieuPOJO> ds = PhieuDAO.getDanhSach();
        int SoLuong = ds.size();
        sotrang = SoLuong / soluongSPtrenTrang;
        if(SoLuong%soluongSPtrenTrang!=0)
            sotrang++;
        return sotrang;
    }
   
    public static int tinhsoluongTrangKMTruoc() throws FileNotFoundException, IOException
    {
        int sotrang=0;
        int soSPtrenTrang = PhieuDAO.laySoLuongSanPham(); 
        int soluongSPtrenTrang = soSPtrenTrang;
        ArrayList<PhieuPOJO> ds = PhieuDAO.getDanhSachKMTruoc();
        int SoLuong = ds.size();
        sotrang = SoLuong / soluongSPtrenTrang;
        if(SoLuong%soluongSPtrenTrang!=0)
            sotrang++;
        return sotrang;
    }
    
   public static int tinhSoLuongTrangLoai(int maDM) throws FileNotFoundException, IOException {
        int sotrang=0;
        int soSPtrenTrang = PhieuDAO.laySoLuongSanPham(); 
        int soluongSPtrenTrang = soSPtrenTrang;
        ArrayList<PhieuPOJO> ds = PhieuDAO.getDanhSachLoai(maDM);
        int SoLuong = ds.size();
        sotrang = SoLuong / soluongSPtrenTrang;
        if(SoLuong%soluongSPtrenTrang!=0)
            sotrang++;
        return sotrang;
   }
    
    public static int tinhSoLuongTrangTK(String tukhoa) throws FileNotFoundException, IOException {
        int sotrang=0;
        int soSPtrenTrang = PhieuDAO.laySoLuongSanPham(); 
        int soluongSPtrenTrang = soSPtrenTrang;
        ArrayList<PhieuPOJO> ds = PhieuDAO.getDanhSachTimKiem(tukhoa);
        int SoLuong = ds.size();
        sotrang = SoLuong / soluongSPtrenTrang;
        if(SoLuong%soluongSPtrenTrang!=0)
            sotrang++;
        return sotrang;
   }

    
    public static int CapNhatSoLuongMua(PhieuPOJO s, int ms, MySQLProviders helper)
    {
        
        String sql = String.format("UPDATE Phieu SET SoLuongMua = '%d' where MaPhieu = %d",s.getSoLuongMua(),ms );
        int n = helper.executeUpdate(sql);
        if( n== 1)
            return 1;
        else
            return 0;
    }
    
    public static int CapNhatSoLuongHuy(PhieuPOJO s, int ms, MySQLProviders helper)
    {
        
        String sql = String.format("UPDATE Phieu SET SoLuongMua = '%d' where MaPhieu = %d",s.getSoLuongMua(),ms );
        int n = helper.executeUpdate(sql);
        if( n== 1)
            return 1;
        else
            return 0;
    }
    
    public static ArrayList<PhieuPOJO> getDanhSachCoPhanTrang(int trang) throws FileNotFoundException, IOException {
      ArrayList<PhieuPOJO> ds = new ArrayList<PhieuPOJO>(); 
      int soSanPhamTrenTrang = ThamSoDAO.laySoLuongTrenTrang();
      int batDau = (trang - 1) * soSanPhamTrenTrang;
      if(soSanPhamTrenTrang==batDau)
          batDau=1;     
      //mo ket noi
      MySQLProviders helper = new MySQLProviders();
      helper.open();
      
      //doc query tu file text
      //lay cau truy van tu file text
     MySQLQuery mySQLQuery=new MySQLQuery();
     mySQLQuery.Read("PHANTRANG");
     String sql=String.format(mySQLQuery.GetQuery(MySQLQuery.QueryKey.SELECT_ALL_PHIEU_PHANTRANG),batDau,soSanPhamTrenTrang);
      ResultSet rs = helper.executeQuery(sql);
      try {
         while (rs.next()) {
            PhieuPOJO temp = new PhieuPOJO();
            temp.setMaPhieu(rs.getInt("MaPhieu"));
            temp.setTenPhieu(rs.getString("TenPhieu"));
            temp.setMoTa(rs.getString("MoTa"));
            temp.setLuuY(rs.getString("LuuY"));
            temp.setGiaBan(rs.getDouble("GiaBan"));
            temp.setGiaGoc(rs.getDouble("GiaGoc"));
            temp.setGiamGia(rs.getInt("GiamGia"));
            temp.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
            temp.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
            temp.setSoLuongMua(rs.getInt("SoLuongMua"));
            temp.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));               
            temp.setHinhChinh(rs.getString("HinhChinh"));
            temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));               
            temp.setMaDanhMuc(rs.getInt("MaDanhMuc"));
            temp.setMaNCC(rs.getInt("MaNCC"));
            temp.setAnHien(rs.getBoolean("AnHien"));
            ds.add(temp);
         }
      } catch (SQLException ex) {
         System.out.println(ex.getMessage());
      }
      helper.close();
      return ds;
   }
    
    
    public static PhieuPOJO getById(int id){
       PhieuPOJO obj=new PhieuPOJO();
       MySQLProviders helper = new MySQLProviders();
       helper.open();
       ResultSet rs = helper.executeQuery("select *from phieu where maphieu="+id);
       try {
           while (rs.next()) {            
            obj.setMaPhieu(rs.getInt("MaPhieu"));
            obj.setTenPhieu(rs.getString("TenPhieu"));
            obj.setMoTa(rs.getString("MoTa"));
            obj.setLuuY(rs.getString("LuuY"));
            obj.setGiaBan(rs.getDouble("GiaBan"));
            obj.setGiaGoc(rs.getDouble("GiaGoc"));
            obj.setGiamGia(rs.getInt("GiamGia"));
            obj.setBatDauKhuyenMai(rs.getTimestamp("BatDauKhuyenMai"));
            obj.setKetThucKhuyenMai(rs.getTimestamp("KetThucKhuyenMai"));
            obj.setSoLuongMua(rs.getInt("SoLuongMua"));
            obj.setSoLuongQuyDinh(rs.getInt("SoLuongQuiDinh"));               
            obj.setHinhChinh(rs.getString("HinhChinh"));
            obj.setMaTinhTrang(rs.getInt("MaTinhTrang"));               
            obj.setMaDanhMuc(rs.getInt("MaDanhMuc"));
            obj.setMaNCC(rs.getInt("MaNCC"));
            obj.setAnHien(rs.getBoolean("AnHien"));
            
         }
       }catch(Exception e){
           
       }finally{
           helper.close();
       }
       return obj;
   }   
}
